/*****************************************************************************
	SNEGA UTILITIES 0.7a
	Copyright (C) 2009, Xiaohan Tian (snega0223)
	
	http://code.google.com/p/snega-utilities/
	
	This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License(LGPL) as
	published by the Free Software Foundation, either version 3 of the 
	License, or(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU Lesser General Public License for more details.
	
	You should have received a copy of the GNU Lesser General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
*****************************************************************************/

package org.sng.util.db;

import java.io.*;

import org.sng.util.db.rawsql.*;
import org.sng.util.slog.*;

import com.mchange.v2.c3p0.*;

public class GeneralOpForDatabase {
	
	private ComboPooledDataSource m_pooled;
	
	protected GeneralOpForDatabase(ComboPooledDataSource ds)
						throws IOException
	{
		this.m_pooled = ds;
	}
	
	public ComboPooledDataSource GetC3P0Pool() {
		return this.m_pooled;
	}
	
	/* ------ Factory Mode ------ */
	public static GeneralOpForMySQL CreateDboForMySQL(String name, String url, String username, String password) throws IOException
	{
		ComboPooledDataSource pooled = null;
		
		try
		{
			pooled = new ComboPooledDataSource(); 
			pooled.setDriverClass("com.mysql.jdbc.Driver"); //loads the jdbc driver 
			pooled.setJdbcUrl(url); 
			pooled.setUser(username); 
			pooled.setPassword(password); // the settings below are optional -- c3p0 can work with defaults 
			pooled.setAcquireIncrement(5); 
			pooled.setMaxPoolSize(30); 
			pooled.setTestConnectionOnCheckin(true);
			pooled.setIdleConnectionTestPeriod(60);
			
			SLog.println("General Database Operation For MySQL created: " + "com.mysql.jdbc.Driver" + ", " + url + ", " + username + ", " + password);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		GeneralOpForMySQL dbo = new GeneralOpForMySQL(pooled);
		
		DboPool.PushDbo(name, dbo);
		
		return dbo;
	}
	
	public static GeneralOpForDatabase CreateDboForDatabase(String name, String engine, String url, String username, String password) throws IOException
	{
		ComboPooledDataSource pooled = null;
		
		try
		{
			pooled = new ComboPooledDataSource(); 
			pooled.setDriverClass(engine); //loads the jdbc driver 
			pooled.setJdbcUrl(url); 
			pooled.setUser(username); 
			pooled.setPassword(password); // the settings below are optional -- c3p0 can work with defaults 
			pooled.setAcquireIncrement(5); 
			pooled.setMaxPoolSize(30); 
			pooled.setTestConnectionOnCheckin(true);
			pooled.setIdleConnectionTestPeriod(60);
			
			SLog.println("General Database Operation created: " + engine + ", " + url + ", " + username + ", " + password);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		GeneralOpForDatabase dbo = new GeneralOpForDatabase(pooled);
		
		DboPool.PushDbo(name, dbo);
		
		return dbo;
	}
	
	/* ------ General SQL Builder ------ */
	public SQLSelect CreateSelect(String s)
	{
		SQLSelect o = RawSQL.CreateSelect(s);
		o.SetDbo(this);
		
		return o;
	}
	
	public SQLInsert CreateInsert(String s)
	{
		SQLInsert o = RawSQL.CreateInsert(s);
		o.SetDbo(this);
		
		return o;
	}
	
	public SQLUpdate CreateUpdate(String s)
	{
		SQLUpdate o = RawSQL.CreateUpdate(s);
		o.SetDbo(this);
		
		return o;
	}
	
	public SQLDelete CreateDelete(String s)
	{
		SQLDelete o = RawSQL.CreateDelete(s);
		o.SetDbo(this);
		
		return o;
	}
	
	
	public String BuildLimit(int from, int amount)
	{
		return "";
	}

	/* ------ Methods ------ */
	public DbConn getMetaData()
	{
		DbConn dc = DbConn.get();

		try {
			dc.con = m_pooled.getConnection();
			dc.dmd = dc.con.getMetaData();
			dc.rs = dc.dmd.getTables(null,null,"%",null);
		}
		catch(Exception err) {
			System.out.println(err.toString());
			SLog.println("Abort query matedata!");
			return null;
		}
		
		//SLog.println("Finish query matedata!");
		
		return dc;
	}
	
	public DbConn getPrimaryKeys(String tableName)
	{
		DbConn dc = DbConn.get();
		
		try {
			dc.con = this.m_pooled.getConnection();
			dc.dmd = dc.con.getMetaData();
			dc.rs = dc.dmd.getPrimaryKeys(null, null, tableName);
		} catch (Exception e) {
			SLog.println(e.toString());
			SLog.println("Abort query primary key!");
		}
		
		return dc;
	}
	
	public DbConn executeQuery(String str)
	{
		DbConn dc = DbConn.get();

		try
		{
			dc.con = m_pooled.getConnection();
			dc.stmt = dc.con.createStatement();
			dc.rs = dc.stmt.executeQuery(str);
		}
		catch(Exception err) {
			System.out.println(err.toString());
			SLog.println("Abort Query: '" + str + "'!");
			return null;
		}
		
		//SLog.println("Finish Query: '" + str + "'!");
		
		return dc;
	}
	
	public int executeNonQuery(String str)
	{
		DbConn dc = DbConn.get();
		
		int result = 0;
		
		try
		{
			dc.con = m_pooled.getConnection();
			dc.stmt = dc.con.createStatement();
			result = dc.stmt.executeUpdate(str);
		}
		catch(Exception err) {
			System.out.println(err.toString());
			SLog.println("Abort Query: '" + str + "'!");
			return result;
		}
		
		//SLog.println("Finish Query: '" + str + "'!");
		
		dc.close();
		
		return result;
	}
}
